-- drop function if exists readbowtie_append(text, text);

create or replace function readbowtie_append(infile text, job_id text)
RETURNS VOID AS $$

------------------[ readbowtie_append(infile, job_id) ]----------------------
--
-- Import the output file of bowtie alignment (in standard format) and append
-- it to existing bowtie table.
--
-- Usage:
-- select readbowtie('C:/path/to/file', 'a_name_for_this_job_alignment')
-- Use forward slahes in the path to file also on Windows 

-- See also http://bowtie-bio.sourceforge.net/manual.shtml
-------------------------------------------------------------------------------

  DECLARE 
    rowcount int;
      
  BEGIN
  -- Create temp storage table
  EXECUTE 
  'CREATE TEMP TABLE tmp_bowtie (
     read_name character varying,
     strand varchar,
     refseq_name character varying,
     "position" int,
     read_sequence character varying,
     read_quality character varying,
     no_inst 	int,
     list_mism character varying
     )';
    
  -------------------------[ Import result file]-------------------------------
  EXECUTE
  'COPY tmp_bowtie FROM ''' || infile || E''' WITH CSV DELIMITER E''\t''';

  RAISE NOTICE 'Import from % done', infile;

  -- Update strand column to comply with boolean datatype
  EXECUTE
   'UPDATE tmp_bowtie SET strand = TRUE where strand like ''+''';
  EXECUTE
   'UPDATE tmp_bowtie SET strand = FALSE where strand like ''-''';

  RAISE NOTICE 'Started appending rows to table bowtie...';

  -- Append to table bowtie
  EXECUTE
  'INSERT INTO bowtie SELECT ''' || job_id || ''', read_name, 
    strand::boolean, refseq_name, "position", read_sequence, read_quality, 
    no_inst, list_mism 
    FROM tmp_bowtie';

  ---------------------[ Announcement at the end of query ]--------------------

  SELECT COUNT(*) INTO rowcount FROM tmp_bowtie;

  drop table if exists tmp_bowtie;
  
  RAISE NOTICE  '% rows appended to table bowtie', rowcount;

  END;
$$ LANGUAGE 'plpgsql' VOLATILE;